package com.my.util;

import com.my.domain.dto.Column;
import com.my.domain.dto.Table;
import lombok.extern.log4j.Log4j;


import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * Created by Administrator on 2017/6/12.
 */
@Log4j
public class DB {
    private DB(){
    }



    private static final DB db = new DB();

    Connection connection = null;
    Statement state = null;
    PreparedStatement preparedStatement = null;
    ResultSet set = null;

    public static DB getInstance(){
        return db;
    }

    public String appendMysqlUrl(String ipPort, String databaseName){
        StringBuilder sb = new StringBuilder("jdbc:mysql://");
        sb.append(ipPort).append("/").append(databaseName)
                .append("?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&useSSL=true");
        return sb.toString();
    }

    /**
     * build mysql connection
     * @return
     */
    public Connection buildDbConnection(String url, String userName, String pwd) throws SQLException,ClassNotFoundException{

        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(url, userName, pwd);
        return connection;
    }


    /**
     * 获取指定数据库中所有表
     * @param connection
     * @param dbName
     * @return
     * @throws SQLException
     */
    public Map<String, Table> getTables(Connection connection, String dbName) throws SQLException{
        StringBuilder sql = new StringBuilder()
                .append("select table_name from information_schema.tables where table_schema='").append(dbName)
                .append("' and table_type='base table'");
        String s = sql.toString();
        log.info("sql:"+s);
        Table table = null;
        try ( Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql.toString())) {
            //
            resultSet.last();//移到最后一行
            int count = resultSet.getRow();
            resultSet.beforeFirst();//移到初始位置

            Map<String,Table> tableMap = new HashMap<>(count);
            while (resultSet.next()) {
                String tableName = resultSet.getString(1);
                table = new Table(tableName);
                tableMap.put(tableName,table);
            }
            if (tableMap.isEmpty())
                return Collections.emptyMap();
            return tableMap;
        }
    }


    /**
     * 查询 数据库 数据表 中列 属性信息
     * @param connection
     * @param dbName
     * @param tableName
     * @return
     */
    public Map<String, Column> getTableColumns(Connection connection, String dbName, String tableName) throws SQLException{
        StringBuilder sb = new StringBuilder()
                .append("select column_name, data_type, (case when character_maximum_length != 'NULL' then character_maximum_length when numeric_precision != 'NULL' then numeric_precision else 0 end) length, if(column_key='PRI', 1, 0) column_key,if(is_nullable='NO', 0, 1) is_nullable from information_schema.columns where table_schema='")
                .append(dbName).append("' and table_name='").append(tableName).append("'");

        try ( Statement statement = connection.createStatement();
              ResultSet resultSet = statement.executeQuery(sb.toString())) {

            resultSet.last();//移到最后一行
            int count = resultSet.getRow();
            resultSet.beforeFirst();//移到初始位置
            //..
            Map<String, Column> columnMap = new HashMap<>(count);
            Column column = null;
            while (resultSet.next()) {
                //...
                column = new Column.Builder(resultSet.getString(1), resultSet.getString(2))
                        .columnLength(Integer.valueOf(resultSet.getString(3))).columnIsPri(Integer.valueOf(resultSet.getString(4)))
                        .columnIsNull( Integer.valueOf(resultSet.getString(5))).build();

                columnMap.put(resultSet.getString(1), column);
            }
            if (columnMap.isEmpty())
                return Collections.emptyMap();
            return columnMap;
        }
    }

    /**
     * close mysql connection
     * @throws SQLException
     */
    public void closeDbConnection() throws SQLException{
        if (connection != null) {
            connection.close();
            connection = null;
        }
    }


    public static void backUpSql(String mysqlBinInstallUrl, String host, String mysqlRoot, String rootPwd, String dbName, String outFullUrl){
        String command = mysqlBinInstallUrl + "mysqldump -h " + host + " -u" + mysqlRoot + " -p" + rootPwd + " " + dbName;
        BufferedReader reader = null;
        FileOutputStream fos = null;
        PrintWriter pw = null;
        InputStreamReader inputStreamReader = null;
        try {
            Process process = Runtime.getRuntime().exec(command);
            inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");
            reader = new BufferedReader(inputStreamReader);
            fos = new FileOutputStream(outFullUrl);
            pw = new PrintWriter(new OutputStreamWriter(fos,"utf8"));
            String line;
            while ((line = reader.readLine()) != null) {
                pw.print(line + "\r\n");
            }
            pw.flush();
        }catch (Exception e){
            log.error("stream deal fail",e);
        }finally {
            try {
                if (pw != null) {
                    pw.close();
                }
                if (fos != null) {
                    fos.close();
                }
                if (reader != null) {
                    reader.close();
                }
                if (inputStreamReader != null) {
                    inputStreamReader.close();
                }
            }catch (Exception e){
                log.error("stream close fail",e);
            }
        }

    }

    public static void main(String [] args){

        backUpSql("E://mysql-5.7.13-winx64//bin//","localhost","root","123456","sshjpa","f:/test.sql");
    }

}
